# -*- coding: utf-8 -*-
import pymysql
import os
import time

config = {
          'host':'120.76.25.102',
          'port':3306,
          'user':'crm',
          'password':'sdc',
          'db':'ccihmainst',
          'charset':'utf8',
          'cursorclass':pymysql.cursors.DictCursor,
          }



def Select(_request):
    '''
    编写：宋德聪
    功能：执行查询语句返回列表
    '''
    objReturn = {"Value": "", "ReturnValue": "", "IsError": "", "ErrorCode": "", "ErrorMsg": "","RowNum":"0"}
    try:
        conn = pymysql.connect(**config)
        cur = conn.cursor()
        cur.execute(_request)
        data = cur.fetchall()
        if data:
            objReturn["Value"] = data
            objReturn["ReturnValue"] = True
            objReturn["IsError"] = False
            objReturn["ErrorCode"] = 0
            objReturn["ErrorMsg"] = ""
            objReturn["RowNum"]=len(data)
        else:
            objReturn["Value"] = ""
            objReturn["ReturnValue"] = False
            objReturn["IsError"] = True
            objReturn["ErrorCode"] = 1
            objReturn["ErrorMsg"] = "没能查询到匹配的值"
            objReturn["RowNum"] = 0
    except pymysql.Error,e:
        objReturn["Value"] = 0
        objReturn["ReturnValue"] = False
        objReturn["IsError"] = True
        objReturn["ErrorCode"] = e.args[0]
        objReturn["ErrorMsg"] = e.args[1]
        objReturn["RowNum"] = 0
        f = open("[" + time.strftime('%Y%m%d%H%M%S') + "]Error", "a")
        f.write("[" + time.strftime('%Y-%m-%d %H:%M:%S') + "]" + "\n")
        f.write("[" + _request + "]" + "\n")
        f.write("[" + e.args[1] + "]" + "\n")
        f.close()

    cur.close()
    conn.close()
    return objReturn

def Exec(_request):
    '''
    编写：宋德聪
    功能：执行SQL指令返回True或False
    '''
    objReturn = {"Value": "", "ReturnValue": "", "IsError": "", "ErrorCode": "", "ErrorMsg": ""}
    try:
        conn = pymysql.connect(**config)
        cur = conn.cursor()
        doNum = cur.execute(_request)
        if doNum == 0:
            objReturn["Value"] = 0
            objReturn["ReturnValue"] = False
            objReturn["IsError"] = True
            objReturn["ErrorCode"] = 1
            objReturn["ErrorMsg"] = "影响行数为0！更新信息与原来一致或没有符合判断条件的信息！"
        else:
            objReturn["Value"] = doNum
            objReturn["ReturnValue"] = True
            objReturn["IsError"] = False
            objReturn["ErrorCode"] = 0
            objReturn["ErrorMsg"] = ""
    except pymysql.Error,e:
        objReturn["Value"] = 0
        objReturn["ReturnValue"] = False
        objReturn["IsError"] = True
        objReturn["ErrorCode"] = e.args[0]
        objReturn["ErrorMsg"] = e.args[1]
        f = open("["+time.strftime('%Y%m%d%H%M%S')+"]Error","a")
        f.write("["+time.strftime('%Y-%m-%d %H:%M:%S')+"]" + "\n")
        f.write("["+_request+"]" + "\n")
        f.write("["+e.args[1]+"]" + "\n")
        f.close()

    cur.close()
    conn.close()
    return objReturn

def GetNewids(_TableName):
    '''
    编写：宋德聪
    功能：获取表主键ID
    参数：_TableName 表名
    '''
    objReturn = {"Value": "", "ReturnValue": "", "IsError": "", "ErrorCode": "", "ErrorMsg": ""}
    try:
        conn = pymysql.connect(**config)
        cur = conn.cursor()
        cur.nextset()
        cur.execute(" call GetNewids('" + _TableName + "') ")
        conn.commit() #提交,如果不加这句，存储过程会被模拟执行一次，有返回结果，但是在MySql中不会真正执行
        data = cur.fetchall()
        if data:
            objReturn["Value"]= str(data[0]['maxkey'])
            objReturn["ReturnValue"]=True
            objReturn["IsError"]=False
            objReturn["ErrorCode"] = 0
            objReturn["ErrorMsg"] = ""
        else:
            objReturn["Value"] = 0
            objReturn["ReturnValue"] = False
            objReturn["IsError"] = True
            objReturn["ErrorCode"] = 1
            objReturn["ErrorMsg"]="存储过程执行失败！未能获取到ID值！"
    except pymysql.Error, e:
        objReturn["Value"] = 0
        objReturn["ReturnValue"] = False
        objReturn["IsError"] = True
        objReturn["ErrorCode"]= e.args[0]
        objReturn["ErrorMsg"] = e.args[1]



    cur.close()
    conn.close()
    return objReturn

def GetSomeIDs(_TableName,_NeedNum):
    '''
    编写：宋德聪
    功能：批量获取表新增ID
    参数：_TableName 表名 | _NeedNum 需要获取的ID数量
    '''
    objReturn = {"Value": "", "ReturnValue": "", "IsError": "", "ErrorCode": "", "ErrorMsg": ""}
    try:
      conn = pymysql.connect(**config)
      cur = conn.cursor()
      cur.nextset()
      cur.execute(" call GetSomeIDs('" + _TableName + "',"+str(_NeedNum)+") ")
      conn.commit()  # 提交,如果不加这句，存储过程会被模拟执行一次，有返回结果，但是在MySql中不会真正执行
      data = cur.fetchall()
      if data:

         objReturn["Value"] = data
         objReturn["numberBegin"] = data[0]["numberBegin"]
         objReturn["numberEnd"]=data[0]["numberEnd"]
         a = data[0]["numberBegin"]
         b = data[0]["numberEnd"]
         c = range(a,b+1)
         objReturn["numberList"]= c
         objReturn["ReturnValue"] = True
         objReturn["IsError"] = False
         objReturn["ErrorCode"] = 0
         objReturn["ErrorMsg"] = ""
      else:
         objReturn["Value"] = 0
         objReturn["ReturnValue"] = False
         objReturn["IsError"] = True
         objReturn["ErrorCode"] = 1
         objReturn["ErrorMsg"] = "存储过程执行失败！未能获取到ID值！"
    except pymysql.Error, e:
      objReturn["Value"] = 0
      objReturn["ReturnValue"] = False
      objReturn["IsError"] = True
      objReturn["ErrorCode"] = e.args[0]
      objReturn["ErrorMsg"] = e.args[1]

    cur.close()
    conn.close()
    return objReturn

def ff_sql_gotten_insert(_request):
    """
    方法：自动拼接Sql的Insert语句
    :param _request: 传入参数集合
    :param _request["tbaleName"] : 数据表名称
    :param _request["value_normal"] : 字典，{"列名":"列值"}，自动加单引号 和 str()
    :param _request["value_special"] : 字典，{"列名":"列值"}，直接拼接
    :return: 拼接好的Sql语句
    编写：宋德聪
    时间：2016年11月28日 11:39:49
    """
    sqlneed = ""

    sqlneed += """
        insert into """ + _request["tbaleName"] + """
        ( """

    for xa in _request["value_normal"]:
        sqlneed += """
            """ + xa + ""","""
    for xb in _request["value_special"]:
        sqlneed += """
            """ + xb + ""","""
    sqlneed = sqlneed[:-1]

    sqlneed += """
        )
        values
        ( """
    for xa in _request["value_normal"].itervalues():
        sqlneed += """
            '""" + str(xa) + """',"""
    for xb in _request["value_special"].itervalues():
        sqlneed += """
            """ + xb + ""","""

    sqlneed = sqlneed[:-1]
    sqlneed += """
        ) """
    return sqlneed

def ff_sql_gotten_update(_request):
    """
    方法：自动拼接Sql的Update语句
    :param _request:
    :return:
    编写：宋德聪
    时间：2016年11月28日 13:33:26
    """
    sqlneed = ""

    sqlneed +="""
    update """+_request["tbaleName"]+"""
    set """
    for (k, v) in _request["set_normal"].items():
        # print "dict[%s] =" % k, v
        sqlneed +="""
        """+k+""" = '"""+str(v)+"""',"""

    for (k, v) in _request["set_special"].items():
        sqlneed += """
        """ + k + """ = """ + str(v) + ""","""

    # 2个部分总有一个部分有值，拼接好直接删一个就好
    sqlneed = sqlneed[:-1]

    sqlneed += """
    where """

    for (k, v) in _request["where_normal"].items():
        sqlneed += """
        """ + k + """ = '""" + str(v) + """' and"""

    for (k, v) in _request["where_special"].items():
        sqlneed += """
        """ + k + """ = """ + str(v) + """ and"""

    # 2个部分总有一个部分有值，拼接好直接删一个就好
    sqlneed = sqlneed[:-3]

    return sqlneed

def FF_update_table_state_create(tableName,userID,keyID,keyValue):
    """
    临时方法：补录 create_user 和 create_time

    编写：宋德聪
    :param tableName: 数据表名称
    :param userID: 操作账号ID
    :param keyID:   更新关键字
    :param keyValue: 更新的值
    :return:
    """
    sqlupdate = """
    update """+tableName+"""
    set
        create_user = '"""+str(userID)+"""',
        create_time = NOW()
    where
        """+str(keyID)+""" = '"""+str(keyValue)+"""'
    """
    objReturn = Exec(sqlupdate)

def FF_update_table_state_update(tableName,userID,keyID,keyValue):
    """
    临时方法：补录 update_user 和 update_time

    编写：宋德聪
    :param tableName: 数据表名称
    :param userID: 操作账号ID
    :param keyID:   更新关键字
    :param keyValue: 更新的值
    :return:
    """
    sqlupdate = """
    update """+tableName+"""
    set
        update_user = '"""+str(userID)+"""',
        update_time = NOW()
    where
        """+str(keyID)+""" = '"""+str(keyValue)+"""'
    """
    objReturn = Exec(sqlupdate)